package com.zlj.jdbc.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

/**
 * 使用jdbc完成增删改查功能
 * 
 * @author bashen
 *
 */
public class JdbcCRUD {
	@Test
	public void test1() throws ClassNotFoundException, SQLException {
		/*
		 *1、得到四个参数 driverClassName、url、password、username
		 *2、 加载驱动类，获取数据库连接
		 */
		String driverClassName = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/testsql";
		String username = "root";
		String password = "tiger";
		
		Class.forName(driverClassName);  // 加载驱动类
		//获取数据库连接
		Connection conn = DriverManager.getConnection(url, username, password);
		//System.out.println(conn);
		
		/*
		 * 对数据库做增、删、改
		 * 1、通过Connection对象创建Statement
		 * > Statement语句的发送器，它的功能就是向数据库发送sql语句
		 * 2、调用它的int executeUpdate(String sql),它可以发送DDL(create、alter、drop table),
		 *    DML(insert、update、delete )
		 */
		//通过Connection对象得到Statement
		Statement statement = conn.createStatement();
		
		//执行增加操作
		//String sql = "insert into student values(7,'zlj',23,'女')";
		
		//执行修改操作
		//String sql = "update student set sname = 'tom', sage = 24, ssex = '男' where sid = 7";
		
		//执行删除操作
		String sql = "delete from student where sid = 7";
		int i = statement.executeUpdate(sql);
		System.out.println(i);
	}
	
	/*
	 * 执行查询操作
	 */
	@Test
	public void test2() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");  //加载驱动
		//获取数据库连接
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testsql", "root", "tiger");
		//System.out.println(conn);
		
		Statement statement = conn.createStatement();
		String sql = "select * from student";
		ResultSet resultSet = statement.executeQuery(sql);
		
	
		//解析ResultSet
		//把光标向下移动一行，并判断下一行是否存在
		/*
		 while(resultSet.next()) {
		    //通过列编号来获取该列的值
			int sid = resultSet.getInt(1);
			//通过列名称来获取该列的值
			String sname = resultSet.getString("sname");
			int sage = resultSet.getInt("sage");
			String ssex = resultSet.getString("ssex");
			System.out.println(sid + ", " + sname + ", " + sage + ", " + ssex);
		}
		*/
		
		/*
		 * 获取结果集元数据：
		 * 1、得到元数据：resultSet.getMetaData(),返回值为ResultSetMetaData;
		 * 2、获取结果集列数：int getColumnCount();
		 * 3、获取指定列的列名：String getColumnName(int colIndex)
		 */
		int count = resultSet.getMetaData().getColumnCount();
		while(resultSet.next()) {  //遍历行
			for(int i = 1; i <= count; i++) {
				System.out.print(resultSet.getObject(i));
				if(i < count) {
					System.out.print(", ");
				}
			}
			System.out.println();
		}
		
		/*
		 * 关闭资源
		 * 
		 * 关闭资源的一个特点：就是倒关
		 * 先得到的对象后关闭
		 */
		resultSet.close();
		statement.close();
		conn.close();
	}
	
	/*
	 * 标准写法
	 */
	@Test
	public void test3() {
		//定义引用
		Connection conn = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try{
			String driverClassName = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://localhost:3306/testsql";
			String username = "root";
			String password = "tiger";
			Class.forName(driverClassName);
			conn = DriverManager.getConnection(url, username, password); //实例化
			
			//创建statement
			statement = conn.createStatement();
			String sql = "select * from student ";
			resultSet = statement.executeQuery(sql);
			
			/*
			 * 循环遍历resultSet,打印其中的数据
			 * 
			 * getString()和getObject()是通用的！
			 */
			while(resultSet.next()) {
				int sid = resultSet.getInt(1);
				String sname = resultSet.getString(2);
				int sage = resultSet.getInt("sage");
				System.out.println("sid: " + sid + ", sname :" + ", sage :" + sage);
			}
		} catch(Exception e) {
			throw new RuntimeException(e);
		} finally {
				try {
					if(resultSet != null) resultSet.close();
					if(statement != null) statement.close();
					if(conn != null) conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}
}
